package com.purchase.manage.service.impl;

import com.purchase.common.config.PurchaseConfig;
import com.purchase.common.exception.file.InvalidExtensionException;
import com.purchase.common.utils.SecurityUtils;
import com.purchase.common.utils.file.FileUploadUtils;
import com.purchase.common.utils.file.FileUtils;
import com.purchase.common.utils.file.MimeTypeUtils;
import com.purchase.common.utils.uuid.IdUtils;
import com.purchase.manage.damian.DAO.StoreQuotationListEXCELDTO;
import com.purchase.manage.damian.*;
import com.purchase.manage.mapper.ManageFileMapper;
import com.purchase.manage.mapper.ManagePurchaseMapper;
import com.purchase.manage.mapper.ManageSupplierMapper;
import com.purchase.manage.service.IFileService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.rmi.ServerException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author oyi46
 * @version 1.0
 * Create by 2023/7/7 8:17
 */

@Service
public class IFileServiceImpl implements IFileService {

    @Autowired
    ManageFileMapper manageFile;

    @Autowired
    ManagePurchaseMapper managePurchaseMapper;

    @Autowired
    ManageSupplierMapper manageSupplierMapper;

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    //采购员上传的商品类订单 解析清单列表
    @Override
    public void poiReadPurchase(MultipartFile file, OrderList orderList) throws IOException, ParseException {

        Long orderId = IdUtils.randomSnowflake();
        Long purchaseId = managePurchaseMapper.selectPurchaseByuid(SecurityUtils.getUserId());
        Purchase purchase = managePurchaseMapper.selectPurchase(String.valueOf(purchaseId));

        List<StoreQuotationList> list = new ArrayList<>();
        //创建采购物资清单订单
        String fileSuffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        if (".xls".equals(fileSuffix)) {
            list = purchaseRead(file);
            orderList.setOrderId(orderId);
            orderList.setPurchaseName(purchase.getName());
            orderList.setPurchaseId(purchaseId);
            orderList.setStartTimeType(sdf.parse(orderList.getStartTime()));
            orderList.setEndTimeType(sdf.parse(orderList.getEndTime()));
            orderList.setDeliveryTimeType(sdf.parse(orderList.getDeliveryTime()));
            orderList.setWarrantyTimeType(sdf.parse(orderList.getWarrantyTime()));
            orderList.setQuotationTimeType(sdf.parse(orderList.getQuotationTime()));
            if (manageFile.insertOrder(orderList) != 1) {
                throw new ServerException("创建新订单失败，请重新填写表格");
            }
        } else if (".xlsx".equals(fileSuffix)) {
            list = purchaseReadXlsx(file);
            orderList.setOrderId(orderId);
            orderList.setPurchaseName(purchase.getName());
            orderList.setPurchaseId(purchaseId);
            orderList.setStartTimeType(sdf.parse(orderList.getStartTime()));
            orderList.setEndTimeType(sdf.parse(orderList.getEndTime()));
            orderList.setDeliveryTimeType(sdf.parse(orderList.getDeliveryTime()));
            orderList.setWarrantyTimeType(sdf.parse(orderList.getWarrantyTime()));
            orderList.setQuotationTimeType(sdf.parse(orderList.getQuotationTime()));
            if (manageFile.insertOrder(orderList) != 1) {
                throw new ServerException("创建新订单失败，请重新填写表格");
            }
        } else {
            throw new ServerException("上传的文件格式不对，请重新上传");
        }

        if (list != null) {
            for (StoreQuotationList s1 : list) {
                s1.setStoreId(IdUtils.randomSnowflake());
                s1.setOrderId(orderId);
                if (manageFile.insertQuotationList(s1) != 1) {
                    throw new ServerException("创建新订单失败，请重新填写表格");
                }
            }

        } else {
            throw new ServerException("上传的文件数据是空的，请重新上传!");
        }
//        insertSupplierState(orderId, orderList.getTypeIds());
        if (purchase == null) {
            throw new ServerException("该采购员不存在");
        }
    }

    //添加进供应商报价关系表
    private void insertSupplierState(Long orderId, String typeIds) {
        String[] ll = typeIds.split(",");
        for (int i = 0; i < ll.length; i++) {
            String xx = ll[i];
            List<Supplier> supplier = manageFile.selectSu(xx);
//            if (supplier != null) {
//                for (Supplier su : supplier) {
//                    SysSupplierState sys = new SysSupplierState();
//                    sys.setSid(IdUtils.randomSnowflake());
//                    sys.setOrderId(orderId);
//                    sys.setSupplierId(su.getSupplierId());
//                    sys.setCorporateName(su.getCorporateName());
//                    if (manageFile.checkSys(sys) == 0) {
//                        sys.setState("1");
//                        manageFile.insertSys(sys);
//                    }
//                }
//            }
        }
    }

    //供应商上传的商品类订单 解析全部
    @Override
    @Transactional
    public StoreQuotation poiReadSupplier(MultipartFile file, StoreQuotation storeQuotation, MultipartFile photo) throws IOException, InvalidExtensionException {
        String fileSuffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        StoreQuotation store = new StoreQuotation();
        if (".xls".equals(fileSuffix)) {
            store = supplierRead(file);
        } else if (".xlsx".equals(fileSuffix)) {
            store = supplierReadXlsx(file, storeQuotation.getOrderId());
        } else {
            throw new ServerException("上传的文件格式不对，请重新上传");
        }
        

        Long storeQuotationId = IdUtils.randomSnowflake();
        Long supplier = manageSupplierMapper.selectSupplierId(SecurityUtils.getUserId());
        Supplier supplier1 = manageSupplierMapper.selectSupplierById(supplier);
        store.setOrderId(storeQuotation.getOrderId());
        store.setType(storeQuotation.getType());
        store.setStoreQuotationId(storeQuotationId);
        store.setSupplierId(supplier);
        store.setContactsPhone(supplier1.getHandledByPhone());
        store.setQuotationCopyPhoto(FileUploadUtils.upload(PurchaseConfig.getAvatarPath(), photo, MimeTypeUtils.IMAGE_EXTENSION));
        if (manageFile.insertOrderBySupplier(store) != 1) {
            throw new ServerException("上传订单失败，请重新上传");
        }  //添加进订单从表

        List<StoreQuotationList> list = store.getStoreQuotationLists();
        for (StoreQuotationList s1 : list) {
            s1.setStoreId(IdUtils.randomSnowflake());
            s1.setStoreQuotationId(storeQuotationId);
            if (manageFile.insertQuotationListBySupplier(s1) != 1) {
                throw new ServerException("上传订单失败，请重新上传");
            }
        }
//        manageFile.updateSys(storeQuotation.getOrderId(), supplier, "1");
        SysSupplierState sys = new SysSupplierState();
        sys.setSid(IdUtils.randomSnowflake());
        sys.setCorporateName(supplier1.getCorporateName());
        sys.setSupplierId(supplier);
        sys.setOrderId(store.getOrderId());
        sys.setState("1");
        manageFile.insertSys(sys);
        return storeQuotation;
    }

    private StoreQuotation supplierReadXlsx(MultipartFile file, Long orderId) throws IOException {
        InputStream inputStream = file.getInputStream();
        StoreQuotation storeQuotation = new StoreQuotation();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int rowNum = 6;

        int check = manageFile.getCountOrder(orderId);

        List<StoreQuotationList> list = new ArrayList<>();
        try {

            XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
            XSSFSheet sheetAt = sheets.getSheetAt(0);
            XSSFRow row = sheetAt.getRow(1);
            XSSFCell cell = row.getCell(2);
            // 获取公司名称的值
            storeQuotation.setCorporateName(cell.getStringCellValue());

            row = sheetAt.getRow(2);
            cell = row.getCell(2);
            storeQuotation.setContacts(cell.getStringCellValue());

            row = sheetAt.getRow(2);
            cell = row.getCell(6);
            storeQuotation.setContactsPhone(String.valueOf(cell.getNumericCellValue()));

            row = sheetAt.getRow(2);
            cell = row.getCell(12);
            storeQuotation.setEmail(cell.getStringCellValue());

            //获取清单
            row = sheetAt.getRow(3);
            cell = row.getCell(2);
            storeQuotation.setAddress(cell.getStringCellValue());

            int checks = check + 6;
            for (int j = 6; j < checks; j++) {

                row = sheetAt.getRow(j);
                cell = row.getCell(1);

                StoreQuotationList s1 = new StoreQuotationList();
                for (int i = 1; i < 14; i++) {
                    cell = row.getCell(i);
                    if (i == 1) {
                        s1.setProduceName(cell.getStringCellValue());
                    }
                    if (i == 3) {
                        s1.setSpecification(cell.getStringCellValue());
                    }
                    if (i == 5) {
                        s1.setParameter(cell.getStringCellValue());
                    }
                    if (i == 7) {
                        s1.setBrand(cell.getStringCellValue());
                    }
                    if (i == 8) {
                        s1.setUnit(cell.getStringCellValue());
                    }
                    if (i == 9) {
                        String x = String.valueOf(cell.getStringCellValue());
                        Double xx = Double.valueOf(x);
                        BigDecimal num = BigDecimal.valueOf(xx);
                        s1.setNumber(num);
                    }
                    if (i == 11) {
                        s1.setUnitPrice(BigDecimal.valueOf(cell.getNumericCellValue()));
                    }
                    if (i == 12) {
                        s1.setMoney(BigDecimal.valueOf(cell.getNumericCellValue()));
                    }
                    if (i == 13) {
                        s1.setRemakeInfo(cell.getStringCellValue());
                    }
                }
                list.add(s1);
            }

            rowNum = Math.max(checks, 10);

            row = sheetAt.getRow(rowNum);
            cell = row.getCell(5);
            storeQuotation.setCountPrice(BigDecimal.valueOf(cell.getNumericCellValue()));

            row = sheetAt.getRow(rowNum + 1);
            cell = row.getCell(0);
            String dTime = cell.getStringCellValue();
            dTime = dTime.substring(8);
            storeQuotation.setDeliveryTime(sdf.parse(dTime));


            row = sheetAt.getRow(rowNum + 2);
            cell = row.getCell(0);
            String wTime = cell.getStringCellValue();
            wTime = wTime.substring(7);
            storeQuotation.setWarrantyTime(sdf.parse(wTime));


            row = sheetAt.getRow(rowNum + 2);
            cell = row.getCell(7);
            String qTime = cell.getStringCellValue();
            qTime = qTime.substring(6);
            storeQuotation.setQuotationTime(sdf.parse(qTime));

            row = sheetAt.getRow(rowNum + 3);
            cell = row.getCell(0);
            storeQuotation.setRemake(cell.getStringCellValue());

            storeQuotation.setStoreQuotationLists(list);
            return storeQuotation;

        } catch (Exception e) {
            throw new RuntimeException("请按照样式填写，序号可以多增加，但请不要减少");
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    throw new RuntimeException("请按照样式填写，序号可以多增加，但请不要减少");
//                    e.printStackTrace();
                }
            }
        }
    }

    //采购员上传的工程类订单 解析清单列表
    @Override
    public void poiReadPurchaseProject(MultipartFile file, OrderList orderList) throws IOException, ParseException {
        Long orderId = IdUtils.randomSnowflake();
        Long purchaseId = managePurchaseMapper.selectPurchaseByuid(SecurityUtils.getUserId());
        Purchase purchase = managePurchaseMapper.selectPurchase(String.valueOf(purchaseId));
        //识别文件后缀名
        String fileSuffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));

        //创建采购物资清单订单
        List<OrderProjectList> list = new ArrayList<>();
        if (".xls".equals(fileSuffix)) {
            list = purchaseReadProject(file);
            orderList.setOrderId(orderId);
            orderList.setPurchaseName(purchase.getName());
            orderList.setPurchaseId(purchaseId);
            orderList.setAbutmentPhone(Long.valueOf(purchase.getPhone()));
            orderList.setMaterialTypeName("工程类");
            orderList.setStartTimeType(sdf.parse(orderList.getStartTime()));
            orderList.setEndTimeType(sdf.parse(orderList.getEndTime()));
            if (manageFile.insertOrder(orderList) != 1) {
                throw new ServerException("创建新订单失败，请重新填写表格");
            }
        } else if (".xlsx".equals(fileSuffix)) {
            list = purchaseReadProjectXlsx(file);
            orderList.setOrderId(orderId);
            orderList.setPurchaseName(purchase.getName());
            orderList.setPurchaseId(purchaseId);
            orderList.setAbutmentPhone(Long.valueOf(purchase.getPhone()));
            orderList.setMaterialTypeName("工程类");
            orderList.setStartTimeType(sdf.parse(orderList.getStartTime()));
            orderList.setEndTimeType(sdf.parse(orderList.getEndTime()));
            if (manageFile.insertOrder(orderList) != 1) {
                throw new ServerException("创建新订单失败，请重新填写表格");
            }
        } else {
            throw new ServerException("上传的文件格式不对，请重新上传");
        }

        for (OrderProjectList o1 : list) {
            o1.setProjectId(IdUtils.randomSnowflake());
            o1.setOrderId(orderId);
            if (manageFile.insertProjectQuotationList(o1) != 1) {
                throw new ServerException("创建新订单失败，请重新填写表格");
            }
        }
//        insertSupplierState(orderId, orderList.getTypeIds());
    }

    private List<OrderProjectList> purchaseReadProjectXlsx(MultipartFile file) throws IOException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int rowNum = 3;
        InputStream inputStream = file.getInputStream();
        List<OrderProjectList> list = new ArrayList<>();

        try {
            XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
            XSSFSheet sheetAt = sheets.getSheetAt(0);
            XSSFRow row = sheetAt.getRow(1);
            XSSFCell cell = row.getCell(2);

            for (int j = 3; j < 100; j++) {
                row = sheetAt.getRow(j);

                if (row == null || row.equals("")) {
                    rowNum = j;
                    break;
                } else {
                    cell = row.getCell(1);
                    String xx = cell.getStringCellValue();
                    if ("".equals(xx)) {
                        rowNum = j;
                        break;
                    }
                }
                cell = row.getCell(1);
                OrderProjectList o1 = new OrderProjectList();
                for (int i = 1; i < 8; i++) {
                    cell = row.getCell(i);
                    if (i == 1) {
                        o1.setMaintenanceItems(cell.getStringCellValue());
                    }
                    if (i == 2) {
                        o1.setContent(cell.getStringCellValue());
                    }
                    if (i == 3) {
                        o1.setUnit(cell.getStringCellValue());
                    }
                    if (i == 4) {
                        o1.setRemakeInfo(cell.getStringCellValue());
                    }
                }
                list.add(o1);
            }
            return list;

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new ServerException("询价单中数据格式不对或者为空");
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    throw new ServerException("询价单中数据格式不对或者为空");
                }
            }
        }
    }

    //供应商上传的工程类订单 解析全部
    @Override
    @Transactional
    public OrderProject poiReadSupplierProject(MultipartFile file, Long orderId, MultipartFile photo) throws IOException, InvalidExtensionException {
        String fileSuffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        OrderProject order = new OrderProject();
        if (".xls".equals(fileSuffix)) {
            order = supplierReadSupplier(file);
        } else if (".xlsx".equals(fileSuffix)) {
            order = supplierReadSupplierXlsx(file, orderId);
        } else {
            throw new ServerException("上传的文件格式不对，请重新上传");
        }

        Long projectid = IdUtils.randomSnowflake();

        Long supplier = manageSupplierMapper.selectSupplierId(SecurityUtils.getUserId());
        order.setOrderId(orderId);
        order.setProjectId(projectid);
        order.setSupplierId(supplier);
        order.setQuotationCopyPhoto(FileUploadUtils.upload(PurchaseConfig.getAvatarPath(), photo, MimeTypeUtils.IMAGE_EXTENSION));
        if (manageFile.insertOrderBySupplierProject(order) != 1) {
            throw new ServerException("上传订单失败，请重新上传");
        }

        List<OrderProjectList> list = order.getOrderProjectLists();
        for (OrderProjectList o1 : list) {
            o1.setProjectId(IdUtils.randomSnowflake());
            o1.setProjectQuotationId(projectid);
            if (manageFile.insertProjectQuotationList(o1) != 1) {
                throw new ServerException("上传订单失败，请重新上传");
            }
        }
        Supplier supplier1 = manageSupplierMapper.selectSupplierById(supplier);
        SysSupplierState sys = new SysSupplierState();
        sys.setSid(IdUtils.randomSnowflake());
        sys.setCorporateName(supplier1.getCorporateName());
        sys.setSupplierId(supplier);
        sys.setOrderId(orderId);
        sys.setState("1");
        manageFile.insertSys(sys);
        return order;
    }

    private OrderProject supplierReadSupplierXlsx(MultipartFile file, Long orderId) throws IOException {
        OrderProject orderProject = new OrderProject();
        InputStream inputStream = file.getInputStream();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int rowNum = 9;
        int check = manageFile.getCountOrderProject(orderId);
        List<OrderProjectList> list = new ArrayList<>();
        try {

            XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
            XSSFSheet sheetAt = sheets.getSheetAt(0);
            XSSFRow row = sheetAt.getRow(1);
            XSSFCell cell = row.getCell(1);

            orderProject.setMaintenanceItems(cell.getStringCellValue());

            row = sheetAt.getRow(2);
            cell = row.getCell(1);
            orderProject.setRepairDate(cell.getDateCellValue());

            row = sheetAt.getRow(2);
            cell = row.getCell(5);
            orderProject.setContacts(cell.getStringCellValue());

            row = sheetAt.getRow(3);
            cell = row.getCell(1);
            orderProject.setCorporateName(cell.getStringCellValue());

            row = sheetAt.getRow(3);
            cell = row.getCell(5);
            Long xx = (long)cell.getNumericCellValue();
            orderProject.setContactsPhone(String.valueOf(xx));

            row = sheetAt.getRow(4);
            cell = row.getCell(1);
            orderProject.setSituation(cell.getStringCellValue());

            row = sheetAt.getRow(5);
            cell = row.getCell(1);
            orderProject.setRepairLocation(cell.getStringCellValue());

            row = sheetAt.getRow(5);
            cell = row.getCell(5);
            orderProject.setAbutmentPhone((long) cell.getNumericCellValue());

            int checks = check + 9;

            for (int j = 9; j < checks; j++) {
                row = sheetAt.getRow(j);
                cell = row.getCell(1);
                OrderProjectList o1 = new OrderProjectList();
                for (int i = 1; i < 8; i++) {
                    cell = row.getCell(i);
                    if (i == 1) {
                        o1.setMaintenanceItems(cell.getStringCellValue());
                    }
                    if (i == 2) {
                        o1.setContent(cell.getStringCellValue());
                    }
                    if (i == 3) {
                        o1.setUnit(cell.getStringCellValue());
                    }
                    if (i == 4) {
                        o1.setNumber(BigDecimal.valueOf(cell.getNumericCellValue()));
                    }
                    if (i == 5) {
                        o1.setTaxPrice(BigDecimal.valueOf(cell.getNumericCellValue()));
                    }
                    if (i == 6) {
                        o1.setCombinedPrice(BigDecimal.valueOf(cell.getNumericCellValue()));
                    }
                    if (i == 7) {
                        o1.setRemakeInfo(cell.getStringCellValue());
                    }
                }
                list.add(o1);
            }

            rowNum = Math.max(checks, 14);

            row = sheetAt.getRow(rowNum);
            cell = row.getCell(6);
            orderProject.setCountPrice(BigDecimal.valueOf(cell.getNumericCellValue()));

            orderProject.setOrderProjectLists(list);
            return orderProject;

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("请按照样式填写，序号可以多增加，但请不要减少");
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    throw new RuntimeException("请按照样式填写，序号可以多增加，但请不要减少");
//                    e.printStackTrace();
                }
            }
        }
    }

    //查询 是否重复上传询价单
    @Override
    public void selectCountOrder(Long orderId, String type) throws ServerException {
        Long sid = manageSupplierMapper.selectSupplierId(SecurityUtils.getUserId());
        if ("工程类".equals(type)) {
            if (manageFile.selectCountOrderProject(orderId, sid) != 0) {
                throw new ServerException("已上传工程类询价单，如需重新上传则需要删除");
            }
        } else {
            if (manageFile.selectCountOrder(orderId, sid) != 0) {
                throw new ServerException("已上传商品类询价单，如需重新上传则需要删除");
            }
        }
    }

    //选择用缓冲区来实现这个转换即使用java 创建的临时文件 使用 MultipartFile.transferto()方法 。
    public File transferToFile(MultipartFile multipartFile) {
        File file = null;
        try {
            String originalFilename = multipartFile.getOriginalFilename();
            String[] filename = originalFilename.split("\\.");
            file = File.createTempFile(filename[0], filename[1]);
            multipartFile.transferTo(file);
            file.deleteOnExit();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return file;
    }

    // 读取excel 商品类报价单（来自供应商）的某行某格的数据数据
    public StoreQuotation supplierRead(MultipartFile file) throws IOException {
        StoreQuotation storeQuotation = new StoreQuotation();
        FileInputStream xlsStream = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int rowNum = 6;
        List<StoreQuotationList> list = new ArrayList<>();
        try {
            // Excel工作簿 输入流
            xlsStream = new FileInputStream(transferToFile(file));
            // 构造工作簿对象
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(xlsStream);
            // 获取工作表,这里获取的是第一个sheet，
            // 如果一个工作薄对象有多个sheet的话，就需要遍历获取多个
            HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);
            // 获取行,行号作为参数传递给getRow方法
            HSSFRow row = sheetAt.getRow(1);
            // 获取单元格,row已经确定了行号,列号作为参数传递给getCell，就可以获得相应的单元格了
            HSSFCell cell = row.getCell(2);
            // 获取公司名称的值
            storeQuotation.setCorporateName(cell.getStringCellValue());

            row = sheetAt.getRow(2);
            cell = row.getCell(2);
            storeQuotation.setContacts(cell.getStringCellValue());

            row = sheetAt.getRow(2);
            cell = row.getCell(6);
            storeQuotation.setContactsPhone(String.valueOf(cell.getNumericCellValue()));

            row = sheetAt.getRow(2);
            cell = row.getCell(12);
            storeQuotation.setEmail(cell.getStringCellValue());

            //获取清单
            row = sheetAt.getRow(3);
            cell = row.getCell(2);
            storeQuotation.setAddress(cell.getStringCellValue());

            for (int j = 6; j < 100; j++) {
                row = sheetAt.getRow(j);
                cell = row.getCell(1);
                String produceName1 = cell.getStringCellValue();
                if ("".equals(produceName1)) {
                    rowNum = j;
                    break;
                }
                StoreQuotationList s1 = new StoreQuotationList();
                for (int i = 1; i < 14; i++) {
                    cell = row.getCell(i);
                    if (i == 1) {
                        s1.setProduceName(cell.getStringCellValue());
                    }
                    if (i == 3) {
                        s1.setSpecification(cell.getStringCellValue());
                    }
                    if (i == 5) {
                        s1.setParameter(cell.getStringCellValue());
                    }
                    if (i == 7) {
                        s1.setBrand(cell.getStringCellValue());
                    }
                    if (i == 8) {
                        s1.setUnit(cell.getStringCellValue());
                    }
                    if (i == 9) {
                        s1.setNumber(BigDecimal.valueOf(cell.getNumericCellValue()));
                    }
                    if (i == 11) {
                        s1.setUnitPrice(BigDecimal.valueOf(cell.getNumericCellValue()));
                    }
                    if (i == 12) {
                        s1.setMoney(BigDecimal.valueOf(cell.getNumericCellValue()));
                    }
                    if (i == 13) {
                        s1.setRemakeInfo(cell.getStringCellValue());
                    }
                }
                list.add(s1);
            }

            rowNum = Math.max(rowNum, 10);

            row = sheetAt.getRow(rowNum);
            cell = row.getCell(5);
            storeQuotation.setCountPrice(BigDecimal.valueOf(cell.getNumericCellValue()));

            row = sheetAt.getRow(rowNum + 1);
            cell = row.getCell(0);
            String dTime = cell.getStringCellValue();
            dTime = dTime.substring(7);
            storeQuotation.setDeliveryTime(sdf.parse(dTime));


            row = sheetAt.getRow(rowNum + 2);
            cell = row.getCell(0);
            String wTime = cell.getStringCellValue();
            wTime = wTime.substring(6);
            storeQuotation.setWarrantyTime(sdf.parse(wTime));


            row = sheetAt.getRow(rowNum + 2);
            cell = row.getCell(7);
            String qTime = cell.getStringCellValue();
            qTime = qTime.substring(5);
            storeQuotation.setQuotationTime(sdf.parse(qTime));

            row = sheetAt.getRow(rowNum + 3);
            cell = row.getCell(0);
            storeQuotation.setRemake(cell.getStringCellValue());

            storeQuotation.setStoreQuotationLists(list);
            return storeQuotation;

        } catch (Exception e) {
            throw new RuntimeException("请按照样式填写，序号可以多增加，但请不要减少");
        } finally {
            if (xlsStream != null) {
                try {
                    xlsStream.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    throw new RuntimeException("请按照样式填写，序号可以多增加，但请不要减少");
//                    e.printStackTrace();
                }
            }
        }
    }

    // 读取excel 商品类报价单（来自采购员）的数据数据
    public List<StoreQuotationList> purchaseRead(MultipartFile file) throws IOException {
        FileInputStream xlsStream = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int rowNum = 2;
        List<StoreQuotationList> list = new ArrayList<>();
        try {
            // Excel工作簿 输入流
            xlsStream = new FileInputStream(transferToFile(file));

            // 构造工作簿对象
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(xlsStream);
            // 获取工作表,这里获取的是第一个sheet，
            // 如果一个工作薄对象有多个sheet的话，就需要遍历获取多个
            HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);
            // 获取行,行号作为参数传递给getRow方法
            HSSFRow row = sheetAt.getRow(1);
            // 获取单元格,row已经确定了行号,列号作为参数传递给getCell，就可以获得相应的单元格了
            HSSFCell cell = row.getCell(2);


            for (int j = 2; j < 100; j++) {
                row = sheetAt.getRow(j);
                cell = row.getCell(1);
                String produceName1 = cell.getStringCellValue();
                if ("".equals(produceName1)) {
                    rowNum = j;
                    break;
                }
                StoreQuotationList s1 = new StoreQuotationList();
                for (int i = 1; i < 12; i++) {
                    cell = row.getCell(i);
                    if (i == 1) {
                        s1.setProduceName(cell.getStringCellValue());
                    }
                    if (i == 3) {
                        s1.setSpecification(cell.getStringCellValue());
                    }
                    if (i == 5) {
                        s1.setParameter(cell.getStringCellValue());
                    }
                    if (i == 7) {
                        s1.setBrand(cell.getStringCellValue());
                    }
                    if (i == 8) {
                        s1.setUnit(cell.getStringCellValue());
                    }
                    if (i == 9) {
                        s1.setNumber(BigDecimal.valueOf(cell.getNumericCellValue()));
                    }
                    if (i == 11) {
                        s1.setRemakeInfo(cell.getStringCellValue());
                    }
                }
                list.add(s1);
            }
            return list;

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
//            throw new ServerException("询价单中数据格式不对或者为空");
        } finally {
            if (xlsStream != null) {
                try {
                    xlsStream.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
//                    throw new ServerException("询价单中数据格式不对或者为空");
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    // 读取excel 商品类报价单（来自采购员）的数据数据 xlsx
    public List<StoreQuotationList> purchaseReadXlsx(MultipartFile file) throws IOException {
        int rowNum = 2;
        List<StoreQuotationList> list = new ArrayList<>();
        InputStream inputStream = file.getInputStream();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        try {

            XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
            XSSFSheet sheetAt = sheets.getSheetAt(0);
            XSSFRow row = sheetAt.getRow(1);
            XSSFCell cell = row.getCell(2);

            for (int j = 2; j < 100; j++) {
                row = sheetAt.getRow(j);

                if (row == null || row.equals("")) {
                    rowNum = j;
                    break;
                } else {
                    cell = row.getCell(1);
                    String xx = cell.getStringCellValue();
                    if ("".equals(xx)) {
                        rowNum = j;
                        break;
                    }
                }

                cell = row.getCell(1);
                StoreQuotationList s1 = new StoreQuotationList();
                for (int i = 1; i < 12; i++) {
                    cell = row.getCell(i);
                    if (i == 1) {
                        s1.setProduceName(cell.getStringCellValue());
                    }
                    if (i == 3) {
                        s1.setSpecification(cell.getStringCellValue());
                    }
                    if (i == 5) {
                        s1.setParameter(cell.getStringCellValue());
                    }
                    if (i == 7) {
                        s1.setBrand(cell.getStringCellValue());
                    }
                    if (i == 8) {
                        s1.setUnit(cell.getStringCellValue());
                    }
                    if (i == 9) {
                        s1.setNumber(BigDecimal.valueOf(cell.getNumericCellValue()));
                    }
                    if (i == 11) {
                        s1.setRemakeInfo(cell.getStringCellValue());
                    }
                }
                list.add(s1);
            }
            return list;

        } catch (Exception e) {
            // TODO Auto-generated catch block
//            e.printStackTrace();
            throw new ServerException("询价单中数据格式不对或者为空");
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    throw new ServerException("询价单中数据格式不对或者为空");
//                    e.printStackTrace();
                }
            }
        }
    }

    // 读取excel 工程类报价单（来自采购员）的数据数据
    public List<OrderProjectList> purchaseReadProject(MultipartFile file) throws IOException {
        FileInputStream xlsStream = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int rowNum = 3;
        List<OrderProjectList> list = new ArrayList<>();
        try {
            // Excel工作簿 输入流
            xlsStream = new FileInputStream(transferToFile(file));
            // 构造工作簿对象
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(xlsStream);

            // 获取工作表,这里获取的是第一个sheet，
            // 如果一个工作薄对象有多个sheet的话，就需要遍历获取多个
            HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);


            // 获取行,行号作为参数传递给getRow方法
            HSSFRow row = sheetAt.getRow(1);

            // 获取单元格,row已经确定了行号,列号作为参数传递给getCell，就可以获得相应的单元格了
            HSSFCell cell = row.getCell(2);


            for (int j = 3; j < 100; j++) {
                row = sheetAt.getRow(j);

                cell = row.getCell(1);
                String produceName1 = cell.getStringCellValue();
                if ("".equals(produceName1)) {
                    rowNum = j;
                    break;
                }
                OrderProjectList o1 = new OrderProjectList();
                for (int i = 1; i < 8; i++) {
                    cell = row.getCell(i);
                    if (i == 1) {
                        o1.setMaintenanceItems(cell.getStringCellValue());
                    }
                    if (i == 2) {
                        o1.setContent(cell.getStringCellValue());
                    }
                    if (i == 3) {
                        o1.setUnit(cell.getStringCellValue());
                    }
                    if (i == 4) {
                        o1.setRemakeInfo(cell.getStringCellValue());
                    }
                }
                list.add(o1);
            }
            return list;

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new ServerException("询价单中数据格式不对或者为空");
        } finally {
            if (xlsStream != null) {
                try {
                    xlsStream.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    throw new ServerException("询价单中数据格式不对或者为空");
                }
            }
        }
    }

    // 读取excel 工程类报价单（来自供应商）的某行某格的数据数据
    public OrderProject supplierReadSupplier(MultipartFile file) throws IOException {
        OrderProject orderProject = new OrderProject();
        FileInputStream xlsStream = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int rowNum = 9;
        List<OrderProjectList> list = new ArrayList<>();
        try {
            // Excel工作簿 输入流
            xlsStream = new FileInputStream(transferToFile(file));
            // 构造工作簿对象
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(xlsStream);
            // 获取工作表,这里获取的是第一个sheet，
            // 如果一个工作薄对象有多个sheet的话，就需要遍历获取多个
            HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);

            // 获取行,行号作为参数传递给getRow方法
            HSSFRow row = sheetAt.getRow(1);
            // 获取单元格,row已经确定了行号,列号作为参数传递给getCell，就可以获得相应的单元格了
            HSSFCell cell = row.getCell(1);
            orderProject.setMaintenanceItems(cell.getStringCellValue());

            row = sheetAt.getRow(2);
            cell = row.getCell(1);
            orderProject.setRepairDate(cell.getDateCellValue());

            row = sheetAt.getRow(2);
            cell = row.getCell(5);
            orderProject.setContacts(cell.getStringCellValue());

            row = sheetAt.getRow(3);
            cell = row.getCell(1);
            orderProject.setCorporateName(cell.getStringCellValue());

            row = sheetAt.getRow(3);
            cell = row.getCell(5);
            orderProject.setContactsPhone(String.valueOf(cell.getNumericCellValue()));

            row = sheetAt.getRow(4);
            cell = row.getCell(1);
            orderProject.setSituation(cell.getStringCellValue());

            row = sheetAt.getRow(5);
            cell = row.getCell(1);
            orderProject.setRepairLocation(cell.getStringCellValue());

            row = sheetAt.getRow(5);
            cell = row.getCell(5);
            orderProject.setAbutmentPhone(Long.valueOf((long) cell.getNumericCellValue()));

            for (int j = 9; j < 100; j++) {
                row = sheetAt.getRow(j);

                cell = row.getCell(1);
                String produceName1 = cell.getStringCellValue();
                if ("".equals(produceName1)) {
                    rowNum = j;
                    break;
                }
                OrderProjectList o1 = new OrderProjectList();
                for (int i = 1; i < 8; i++) {
                    cell = row.getCell(i);
                    if (i == 1) {
                        o1.setMaintenanceItems(cell.getStringCellValue());
                    }
                    if (i == 2) {
                        o1.setContent(cell.getStringCellValue());
                    }
                    if (i == 3) {
                        o1.setUnit(cell.getStringCellValue());
                    }
                    if (i == 4) {
                        o1.setNumber(BigDecimal.valueOf(cell.getNumericCellValue()));
                    }
                    if (i == 5) {
                        o1.setTaxPrice(BigDecimal.valueOf(cell.getNumericCellValue()));
                    }
                    if (i == 6) {
                        o1.setCombinedPrice(BigDecimal.valueOf(cell.getNumericCellValue()));
                    }
                    if (i == 7) {
                        o1.setRemakeInfo(cell.getStringCellValue());
                    }
                }
                list.add(o1);
            }

            rowNum = Math.max(rowNum, 14);

            row = sheetAt.getRow(rowNum);
            cell = row.getCell(6);
            orderProject.setCountPrice(BigDecimal.valueOf(cell.getNumericCellValue()));

            orderProject.setOrderProjectLists(list);
            return orderProject;

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("请按照样式填写，序号可以多增加，但请不要减少");
        } finally {
            if (xlsStream != null) {
                try {
                    xlsStream.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    throw new RuntimeException("请按照样式填写，序号可以多增加，但请不要减少");
//                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public OrderProject poiReadSupplierProject11(MultipartFile file) throws IOException, InvalidExtensionException {

        return null;
    }

    //供应商下载工程类询价单
    @Override
    public void supplierDownloadProject(Long orderId, HttpServletResponse response) throws IOException {
        //1.查找报表文件信息
        List<OrderProjectList> list = manageFile.selectReleaseMaterialsListProject(orderId);
        if (list == null) {
            throw new ServerException("该订单 暂无物资信息，请联系管理员");
        }
        //2.读取模板文件并处理值
        //2.1创建 Excel 文件的输入流对象
//        FileInputStream excelFileInputStream = new FileInputStream("D:\\purchase\\downloadPath\\projectInfo.xlsx");
        FileInputStream excelFileInputStream = new FileInputStream("/root/purchase/projectInfo.xlsx");
        // 2.2XSSFWorkbook 就代表一个 Excel 文件，创建其对象，就打开这个 Excel 文件
        XSSFWorkbook workbook = new XSSFWorkbook(excelFileInputStream);
        //2.3读到文件后关闭流
        excelFileInputStream.close();
        // 2.4XSSFSheet 代表 Excel 文件中的一张表格，通过 getSheetAt() 指定表格索引来获取对应表格
        XSSFSheet sheet = workbook.getSheetAt(0);
        //2.5读取处理数据
        //第一行不处理
        //2.5.1第二行,第3列报价单位
        XSSFRow row = sheet.getRow(1);

        //2.5.4判断查询出的数组数量是否大于4,大于4则则需要创建行，否则直接写值
//        int listSize = list.size();
        int listSize = list.size();
        int gap = listSize - 5;
        //gap大于0，则代表数组比4大，且大出了gap值，所以需要创建gap行
        if (gap > 0) {
            //创建行
            sheet.shiftRows(14, sheet.getLastRowNum(), gap);
            //添加序号
            for (int r = 1; r <= gap; r++) {
                sheet.copyRows(13, 13, r + 13, new CellCopyPolicy());
                XSSFRow newRow = sheet.getRow(r + 13);
                newRow = sheet.getRow(r + 13);
                String num = String.valueOf(r + 5);
                newRow.getCell(0).setCellValue(num);
            }
        }
        //2.5.5循环查询列表进行写值
        for (int i = 0; i < list.size(); i++) {
            row = sheet.getRow(9 + i);
            row.getCell(1).setCellValue(list.get(i).getMaintenanceItems());
            row.getCell(2).setCellValue(list.get(i).getContent());
            row.getCell(3).setCellValue(list.get(i).getUnit());
            row.getCell(7).setCellValue(list.get(i).getRemakeInfo());
        }

        //2.5.6第11、12、13行写值（如果查询数组大于4，则在11+gap、12+gap、13+gap行写值）
        if (gap > 0) {
            row = sheet.getRow(14 + gap);
            row = sheet.getRow(15 + gap);
        } else {
            row = sheet.getRow(14);
            row = sheet.getRow(15);
        }

        //3.创建新的文件
//        String downloadPath = "D:\\purchase\\downloadPath\\projectdemo.xlsx";
        String downloadPath = "/root/purchase/projectdemo.xlsx";

        FileOutputStream excelFileOutPutStream = new FileOutputStream(downloadPath);
        //4.将处理完的内容写入新文件
        //4.1写入输入流
        workbook.write(excelFileOutPutStream);
        //4.2将信息更新到文件
        excelFileOutPutStream.flush();
        //4.3关闭输入流
        excelFileOutPutStream.close();
        //5.返回文件

        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        FileUtils.setAttachmentResponseHeader(response, downloadPath);
        FileUtils.writeBytes(downloadPath, response.getOutputStream());

    }

    //供应商下载商品类询价单
    @Override
    public void supplierDownloadStore(Long orderId, HttpServletResponse response) throws IOException, ParseException {

        OrderList order = manageFile.getOrder(orderId);
        //1.查找报表文件信息
        List<StoreQuotationList> list = manageFile.selectReleaseMaterialsListOther(orderId);
        if (list == null) {
            throw new ServerException("该订单 暂无物资信息，请联系管理员");
        }
        //2.读取模板文件并处理值
        //2.1创建 Excel 文件的输入流对象
//        FileInputStream excelFileInputStream = new FileInputStream("D:\\purchase\\downloadPath\\storeInfo.xlsx");
        FileInputStream excelFileInputStream = new FileInputStream("/root/purchase/storeInfo.xlsx");
        // 2.2XSSFWorkbook 就代表一个 Excel 文件，创建其对象，就打开这个 Excel 文件
        XSSFWorkbook workbook = new XSSFWorkbook(excelFileInputStream);
        //2.3读到文件后关闭流
        excelFileInputStream.close();
        // 2.4XSSFSheet 代表 Excel 文件中的一张表格，通过 getSheetAt() 指定表格索引来获取对应表格
        XSSFSheet sheet = workbook.getSheetAt(0);
        //2.5读取处理数据
        //第一行不处理
        //2.5.1第二行,第3列报价单位
        XSSFRow row = sheet.getRow(2);

        //2.5.4判断查询出的数组数量是否大于4,大于4则则需要创建行，否则直接写值
        int listSize = list.size();
        int gap = listSize - 4;
        //gap大于0，则代表数组比4大，且大出了gap值，所以需要创建gap行
        if (gap > 0) {
            //创建行
            sheet.shiftRows(10, sheet.getLastRowNum(), gap);
            //添加序号
            for (int r = 1; r <= gap; r++) {
                sheet.copyRows(9, 9, r + 9, new CellCopyPolicy());
                XSSFRow newRow = sheet.getRow(r + 13);
                newRow = sheet.getRow(r + 9);
                String num = String.valueOf(r + 4);
                newRow.getCell(0).setCellValue(num);
            }
        }
//            //2.5.5循环查询列表进行写值
        for (int i = 0; i < list.size(); i++) {
            row = sheet.getRow(6 + i);
            row.getCell(1).setCellValue(list.get(i).getProduceName());
            row.getCell(3).setCellValue(list.get(i).getSpecification());
            row.getCell(5).setCellValue(list.get(i).getParameter());
            row.getCell(7).setCellValue(list.get(i).getBrand());
            row.getCell(8).setCellValue(list.get(i).getUnit());
            row.getCell(9).setCellValue(String.valueOf(list.get(i).getNumber()));
            row.getCell(13).setCellValue(list.get(i).getRemakeInfo());
        }

        //2.5.6第11、12、13行写值（如果查询数组大于4，则在11+gap、12+gap、13+gap行写值）
        if (gap > 0) {
            row = sheet.getRow(11 + gap);
            row = sheet.getRow(12 + gap);
            row = sheet.getRow(13 + gap);
            row = sheet.getRow(14 + gap);
        } else {
            row = sheet.getRow(11);
            row = sheet.getRow(12);
            row = sheet.getRow(13);
            row = sheet.getRow(14);
        }

        if (gap < 0) {
            gap = 0;
        }

        row = sheet.getRow(11 + gap);
        String detime = "1.交货时间 ：" + sdf.format(sdf.parse(order.getDeliveryTime()));
        row.getCell(0).setCellValue(detime);

        row = sheet.getRow(12 + gap);
        String wtime = "2.质保期 ：" + sdf.format(sdf.parse(order.getWarrantyTime()));
        row.getCell(0).setCellValue(wtime);

        row = sheet.getRow(12 + gap);
        String qtime = "报价日期 ：" + sdf.format(sdf.parse(order.getQuotationTime()));
        row.getCell(7).setCellValue(qtime);


        //3.创建新的文件
//        String downloadPath = "D:\\purchase\\downloadPath\\storedemo.xlsx";
        String downloadPath = "/root/purchase/storedemo.xlsx";
        FileOutputStream excelFileOutPutStream = new FileOutputStream(downloadPath);
        //4.将处理完的内容写入新文件
        //4.1写入输入流
        workbook.write(excelFileOutPutStream);
        //4.2将信息更新到文件
        excelFileOutPutStream.flush();
        //4.3关闭输入流
        excelFileOutPutStream.close();
        //5.返回文件

        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        FileUtils.setAttachmentResponseHeader(response, downloadPath);
        FileUtils.writeBytes(downloadPath, response.getOutputStream());


    }

    //历史订单下载
    @Override
    public List<StoreQuotationListEXCELDTO> priceComparison(String name) {
        if (name != null) {
            name = name.trim();
            if ("undefined".equals(name)) {
                name = null;
            }
        }
        return manageFile.priceComparison(name);
    }

    //测试 待删除
    @Override
    public void insertSupplierState() {

    }


    //采购员下载工程订单
    @Override
    public void purchaseDownProject(Long orderId, Long supplierId, HttpServletResponse response) throws IOException {
        Long pid = manageFile.selectPid(orderId, supplierId);
        //1.查找报表文件信息
        List<OrderProjectList> list = manageFile.selectp(pid);
        if (list == null) {
            throw new ServerException("该订单 暂无物资信息，请联系管理员");
        }

        OrderProject orderProject = manageFile.selectProject(orderId, supplierId);
        //2.读取模板文件并处理值
        //2.1创建 Excel 文件的输入流对象
//        FileInputStream excelFileInputStream = new FileInputStream("D:\\purchase\\downloadPath\\projectInfo.xlsx");
        FileInputStream excelFileInputStream = new FileInputStream("/root/purchase/projectInfo.xlsx");
        // 2.2XSSFWorkbook 就代表一个 Excel 文件，创建其对象，就打开这个 Excel 文件
        XSSFWorkbook workbook = new XSSFWorkbook(excelFileInputStream);
        //2.3读到文件后关闭流
        excelFileInputStream.close();
        // 2.4XSSFSheet 代表 Excel 文件中的一张表格，通过 getSheetAt() 指定表格索引来获取对应表格
        XSSFSheet sheet = workbook.getSheetAt(0);
        //2.5读取处理数据
        //第一行不处理
        //2.5.1第二行,第3列报价单位
        XSSFRow row = sheet.getRow(1);
        row.getCell(1).setCellValue(orderProject.getMaintenanceItems());

        row = sheet.getRow(2);
        row.getCell(1).setCellValue(orderProject.getRepairDate());
        row.getCell(5).setCellValue(orderProject.getContacts());

        row = sheet.getRow(3);
        row.getCell(1).setCellValue(orderProject.getCorporateName());
        row.getCell(5).setCellValue(orderProject.getContactsPhone());

        row = sheet.getRow(4);
        row.getCell(1).setCellValue(orderProject.getSituation());

        row = sheet.getRow(5);
        row.getCell(1).setCellValue(orderProject.getRepairLocation());
        row.getCell(5).setCellValue(orderProject.getAbutmentPhone());

        //2.5.4判断查询出的数组数量是否大于4,大于4则则需要创建行，否则直接写值
//        int listSize = list.size();
        int listSize = list.size();
        int gap = listSize - 5;
        //gap大于0，则代表数组比4大，且大出了gap值，所以需要创建gap行
        if (gap > 0) {
            //创建行
            sheet.shiftRows(14, sheet.getLastRowNum(), gap);
            //添加序号
            for (int r = 1; r <= gap; r++) {
                sheet.copyRows(13, 13, r + 13, new CellCopyPolicy());
                XSSFRow newRow = sheet.getRow(r + 13);
                newRow = sheet.getRow(r + 13);
                String num = String.valueOf(r + 5);
                newRow.getCell(0).setCellValue(num);
            }
        }
        //2.5.5循环查询列表进行写值
        for (int i = 0; i < list.size(); i++) {
            row = sheet.getRow(9 + i);
            row.getCell(1).setCellValue(list.get(i).getMaintenanceItems());
            row.getCell(2).setCellValue(list.get(i).getContent());
            row.getCell(3).setCellValue(list.get(i).getUnit());
            row.getCell(4).setCellValue(String.valueOf(list.get(i).getNumber()));
            row.getCell(5).setCellValue(String.valueOf(list.get(i).getTaxPrice()));
            row.getCell(6).setCellValue(String.valueOf(list.get(i).getCombinedPrice()));
            row.getCell(6).setCellValue(list.get(i).getRemakeInfo());
        }

        //2.5.6第11、12、13行写值（如果查询数组大于4，则在11+gap、12+gap、13+gap行写值）
        if (gap > 0) {
            row = sheet.getRow(14 + gap);
            row = sheet.getRow(15 + gap);
        } else {
            row = sheet.getRow(14);
            row = sheet.getRow(15);
        }
        if (gap < 0) {
            gap = 0;
        }

        row = sheet.getRow(14 + gap);
        String count = String.valueOf(orderProject.getCountPrice());
        row.getCell(6).setCellValue(count);

        //3.创建新的文件
//        String downloadPath = "D:\\purchase\\downloadPath\\projectdemo.xlsx";
        String downloadPath = "/root/purchase/projectdemo.xlsx";
        FileOutputStream excelFileOutPutStream = new FileOutputStream(downloadPath);
        //4.将处理完的内容写入新文件
        //4.1写入输入流
        workbook.write(excelFileOutPutStream);
        //4.2将信息更新到文件
        excelFileOutPutStream.flush();
        //4.3关闭输入流
        excelFileOutPutStream.close();
        //5.返回文件

        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        FileUtils.setAttachmentResponseHeader(response, downloadPath);
        FileUtils.writeBytes(downloadPath, response.getOutputStream());

    }

    //采购员下载商品订单
    @Override
    public void purchaseDownStore(Long orderId, Long supplierId, HttpServletResponse response) throws IOException, ParseException {

        Long stid = manageFile.selectstid(orderId, supplierId);
        //1.查找报表文件信息
        List<StoreQuotationList> list = manageFile.selectstoreList(stid);
        if (list == null) {
            throw new ServerException("该订单 暂无物资信息，请联系管理员");
        }
        StoreQuotation storeQuotation = manageFile.selectStore(orderId, supplierId);
        OrderList orderList = manageFile.getOrder(orderId);

        //2.读取模板文件并处理值
        //2.1创建 Excel 文件的输入流对象
//        FileInputStream excelFileInputStream = new FileInputStream("D:\\purchase\\downloadPath\\storeInfo.xlsx");
        FileInputStream excelFileInputStream = new FileInputStream("/root/purchase/storeInfo.xlsx");
        // 2.2XSSFWorkbook 就代表一个 Excel 文件，创建其对象，就打开这个 Excel 文件
        XSSFWorkbook workbook = new XSSFWorkbook(excelFileInputStream);
        //2.3读到文件后关闭流
        excelFileInputStream.close();
        // 2.4XSSFSheet 代表 Excel 文件中的一张表格，通过 getSheetAt() 指定表格索引来获取对应表格
        XSSFSheet sheet = workbook.getSheetAt(0);
        //2.5读取处理数据
        //第一行不处理
        //2.5.1第二行,第3列报价单位
        XSSFRow row = sheet.getRow(1);
        row.getCell(2).setCellValue(storeQuotation.getCorporateName());

        row = sheet.getRow(2);
        row.getCell(2).setCellValue(storeQuotation.getContacts());
        row.getCell(6).setCellValue(storeQuotation.getContactsPhone());
        row.getCell(12).setCellValue(storeQuotation.getEmail());

        row = sheet.getRow(3);
        row.getCell(2).setCellValue(storeQuotation.getAddress());

        //2.5.4判断查询出的数组数量是否大于4,大于4则则需要创建行，否则直接写值
        int listSize = list.size();
        int gap = listSize - 4;
        //gap大于0，则代表数组比4大，且大出了gap值，所以需要创建gap行
        if (gap > 0) {
            //创建行
            sheet.shiftRows(10, sheet.getLastRowNum(), gap);
            //添加序号
            for (int r = 1; r <= gap; r++) {
                sheet.copyRows(9, 9, r + 9, new CellCopyPolicy());
                XSSFRow newRow = sheet.getRow(r + 13);
                newRow = sheet.getRow(r + 9);
                String num = String.valueOf(r + 4);
                newRow.getCell(0).setCellValue(num);
            }
        }
//            //2.5.5循环查询列表进行写值
        for (int i = 0; i < list.size(); i++) {
            row = sheet.getRow(6 + i);
            row.getCell(1).setCellValue(list.get(i).getProduceName());
            row.getCell(3).setCellValue(list.get(i).getSpecification());
            row.getCell(5).setCellValue(list.get(i).getParameter());
            row.getCell(7).setCellValue(list.get(i).getBrand());
            row.getCell(8).setCellValue(list.get(i).getUnit());
            row.getCell(9).setCellValue(String.valueOf(list.get(i).getNumber()));
            row.getCell(11).setCellValue(String.valueOf(list.get(i).getUnitPrice()));
            row.getCell(12).setCellValue(String.valueOf(list.get(i).getMoney()));
            row.getCell(13).setCellValue(list.get(i).getRemakeInfo());
        }

        //2.5.6第11、12、13行写值（如果查询数组大于4，则在11+gap、12+gap、13+gap行写值）
        if (gap > 0) {
            row = sheet.getRow(11 + gap);
            row = sheet.getRow(12 + gap);
            row = sheet.getRow(13 + gap);
            row = sheet.getRow(14 + gap);
        } else {
            row = sheet.getRow(11);
            row = sheet.getRow(12);
            row = sheet.getRow(13);
            row = sheet.getRow(14);
        }

        if (gap < 0) {
            gap = 0;
        }

        row = sheet.getRow(10 + gap);
        row.getCell(5).setCellValue(storeQuotation.getCorporateName());

        row = sheet.getRow(11 + gap);
//        String x = sdf.format(orderList.getDeliveryTime());
        row.getCell(0).setCellValue("1.交货时间:" + sdf.format(sdf.parse(orderList.getDeliveryTime())));

        row = sheet.getRow(12 + gap);
//        x = sdf.format(orderList.getWarrantyTime());
        row.getCell(0).setCellValue("2.质保期:" +sdf.format(sdf.parse(orderList.getWarrantyTime())));

//        x = sdf.format(orderList.getQuotationTime());
        row.getCell(7).setCellValue("报价日期:" +sdf.format(sdf.parse( orderList.getQuotationTime())));

        //3.创建新的文件 改文件
//        String downloadPath = "D:\\purchase\\downloadPath\\storedemo.xlsx";
        String downloadPath = "/root/purchase/storedemo.xlsx";
        FileOutputStream excelFileOutPutStream = new FileOutputStream(downloadPath);
        //4.将处理完的内容写入新文件
        //4.1写入输入流
        workbook.write(excelFileOutPutStream);
        //4.2将信息更新到文件
        excelFileOutPutStream.flush();
        //4.3关闭输入流
        excelFileOutPutStream.close();
        //5.返回文件

        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        FileUtils.setAttachmentResponseHeader(response, downloadPath);
        FileUtils.writeBytes(downloadPath, response.getOutputStream());
    }
}
